package com.chat.api.utils;

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class PoiReadExcelData {

    /**
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     */
    public static Map readExcel(InputStream inputStream)
    {
        Map map = new LinkedHashMap<>();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy年MM月dd日");
        try {
            //同时支持Excel 2003、2007
            FileInputStream is = (FileInputStream) inputStream;
            Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                List<List<String>> lists = new ArrayList<>();
                Sheet sheet = workbook.getSheetAt(s);
                String sheetName = sheet.getSheetName();
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    List<String> list = new ArrayList<>();
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    //遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        Row row1 = cell.getRow();
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        switch(cellType) {
                            case Cell.CELL_TYPE_STRING: //文本
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                if(DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue()); //日期型
                                }
                                else {
                                    //去除科学计数法
                                    NumberFormat nf = NumberFormat.getInstance();
                                    String value = nf.format(cell.getNumericCellValue());
                                    if (value.indexOf(",") >= 0) {
                                        value = value.replace(",", "");
                                    }
//                                    cellValue = String.valueOf(cell.getNumericCellValue()); //数字
                                    cellValue = value; //数字
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: //空白
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_ERROR: //错误
                                cellValue = "错误";
                                break;
                            case Cell.CELL_TYPE_FORMULA: //公式
                                cellValue = "错误";
                                break;
                            default:
                                cellValue = "错误";
                        }
//                        System.out.print(cellValue + "    ");
                        list.add(cellValue);
                    }
//                    System.out.println();
                    lists.add(list);
                }
                map.put(sheetName,lists);
            }

        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return map;
    }

    /**
     * 上面方法的简写版
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     */
    public String readExcel()
    {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            //同时支持Excel 2003、2007
            File excelFile = new File("/home/zht/test.xls"); //创建文件对象
            FileInputStream is = new FileInputStream(excelFile); //文件流
            Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    //遍历每一个单元格
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;

                        //在读取单元格内容前,设置所有单元格中内容都是字符串类型
                        cell.setCellType(Cell.CELL_TYPE_STRING);

                        //按照字符串类型读取单元格内数据
                        cellValue = cell.getStringCellValue();

                        /*在这里可以对每个单元格中的值进行二次操作转化*/

                        System.out.print(cellValue + "    ");
                    }
                    System.out.println();
                }
            }

        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return null;
    }

}
